Implementing Row Version Verification for Persistence Middleware using SQL Access Patterns

Modern web-based applications are often built as multi-tier architecture using persistence middleware. Middleware technology providers recommend the use of Optimistic Concurrency Control (OCC) mechanism to avoid the risk of blocked resources. However, most vendors of relational database management systems implement only locking schemes for concurrency control. As a consequence a kind of OCC has to be implemented at client or middleware side. The aim of this paper is to recommend Row Version Verification (RVV) as a mean to realize an OCC at the middleware level. To help the developers with the implementation of RVV we propose to use SQL access patterns. For performance reasons the middleware uses buffers (cache) of its own to avoid network traffic and to reduce disk I/O. This caching, however, complicates the use of RVV because the data in the middleware cache may be stale (outdated). We investigate various data access technologies, including the Java Persistence API and Microsoft’s LINQ technologies in combination with commercial database systems for their ability to use the RVV programming discipline. The use of persistence middleware that tries to relieve the programmer from the low level transaction programming turns out to even complicate the situation in some cases. The contribution of this paper are patterns and guidelines for an implementation of OCC at the middleware layer using RVV. Our approach prevents from inconsistencies, reduces locking to a minimum, considers a couple of mainstream technologies, and copes with the effects of concurrency protocols, data access technologies, and caching mechanisms. Keywords-persistence middleware, caching, data access pattern, row version verification.